package com.example.springmvc.dao;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import com.example.springmvc.domain.CompanyInfo;
import com.example.springmvc.domain.InstructorInfo;

@Service("companyDao")
public class CompanyDaoImpl implements DaoBase {

	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	public List selectAll() throws SQLException {
		
		String sql = "SELECT * FROM COMPANY_INFO";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		List<CompanyInfo> results = new ArrayList<CompanyInfo>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
		
		for(Map<String, Object> row : rows){
			CompanyInfo companyInfo = new CompanyInfo();
			companyInfo.setCompany_id((String) row.get("COMPANY_ID"));
			companyInfo.setName((String) row.get("NAME"));
			companyInfo.setBusiness_id((String) row.get("BUSINESS_ID"));
			companyInfo.setPhone_num((String) row.get("PHONE_NUM"));
			companyInfo.setEmail((String) row.get("EMAIL"));
			companyInfo.setCompany_name((String) row.get("COMPANY_NAME"));
			companyInfo.setEmployee_num(((BigDecimal) row.get("EMPLOYEE_NUM")).intValueExact());
			companyInfo.setCategory((String) row.get("CATEGORY"));
			companyInfo.setAddress((String) row.get("ADDRESS"));
			companyInfo.setCompany_phone((String) row.get("COMPANY_PHONE"));
			
			results.add(companyInfo);
		}
		
		return results;
	}

	public Object selectById(String id) throws SQLException {
		
		String sql = "SELECT * FROM COMPANY_INFO WHERE COMPANY_ID=?";
		
		final CompanyInfo companyInfo = new CompanyInfo();
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sql, new Object[]{ id },
				new RowCallbackHandler(){

					public void processRow(ResultSet rs) throws SQLException {
						// Mapping
						companyInfo.setCompany_id(rs.getString("COMPANY_ID"));
						companyInfo.setId(rs.getString("ID"));
						companyInfo.setPasswd(rs.getString("PASSWD"));
						companyInfo.setName(rs.getString("NAME"));
						companyInfo.setBusiness_id(rs.getString("BUSINESS_ID"));
						companyInfo.setPhone_num(rs.getString("PHONE_NUM"));
						companyInfo.setEmail(rs.getString("EMAIL"));
						companyInfo.setCompany_name(rs.getString("COMPANY_NAME"));
						companyInfo.setEmployee_num(rs.getInt("EMPLOYEE_NUM"));
						companyInfo.setCategory(rs.getString("CATEGORY"));
						companyInfo.setAddress(rs.getString("ADDRESS"));
						companyInfo.setCompany_phone(rs.getString("COMPANY_PHONE"));
						
					}
		
				});
		return (Object)companyInfo;
	}

	public boolean update(Object o) throws SQLException {
		CompanyInfo companyInfo = (CompanyInfo)o;
		String sql = "UPDATE COMPANY_INFO " +
				"SET ID = ?, PASSWD = ?, NAME = ?, BUSINESS_ID = ?, PHONE_NUM = ?, " +
				"EMAIL = ?, COMPANY_NAME = ?, EMPLOYEE_NUM = ?, CATEGORY = ?, " +
				"ADDRESS = ?, COMPANY_PHONE = ? " +
				"WHERE COMPANY_ID = ?";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {companyInfo.getId(),
											companyInfo.getPasswd(),
											companyInfo.getName(),
											companyInfo.getBusiness_id(),
											companyInfo.getPhone_num(),
											companyInfo.getEmail(),
											companyInfo.getCompany_name(),
											companyInfo.getEmployee_num(),
											companyInfo.getCategory(),
											companyInfo.getAddress(),
											companyInfo.getCompany_phone(),
											companyInfo.getCompany_id()});
		
		return true;
	}

	public boolean delete(String id) throws SQLException {
		
		String sql = "DELETE FROM COMPANY_INFO " +
				"WHERE COMPANY_ID = ?";
	
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { id });
		
		return true;
		
	}

	public boolean insert(Object o) throws SQLException {
		
		CompanyInfo companyInfo = (CompanyInfo)o;
		String sql = "INSERT INTO COMPANY_INFO(COMPANY_ID, ID, PASSWD, NAME, " +
				"BUSINESS_ID, PHONE_NUM, EMAIL, COMPANY_NAME, EMPLOYEE_NUM, " +
				"CATEGORY, ADDRESS, COMPANY_PHONE) " +
				"VALUES(EMPLOYEES_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {companyInfo.getId(),
											companyInfo.getPasswd(),
											companyInfo.getName(),
											companyInfo.getBusiness_id(),
											companyInfo.getPhone_num(),
											companyInfo.getEmail(),
											companyInfo.getCompany_name(),
											companyInfo.getEmployee_num(),
											companyInfo.getCategory(),
											companyInfo.getAddress(),
											companyInfo.getCompany_phone()});
		
		return false;
		
	}

	public String checkLogIn(String id, String passwd) {
		// TODO Auto-generated method stub
		return null;
	}

}
